# sqlitero.py: A read-only connection to sqlite, for use with the Storm ORM.
#
# Copyright (c) 2007 Richard Boulton
#
# This library is free software; you can redistribute it and/or modify it under
# the terms of the GNU Lesser General Public License as published by the Free
# Software Foundation; either version 2.1 of the License, or (at your option)
# any later version.
#
# This library is distributed in the hope that it will be useful, but WITHOUT
# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License for more
# details.
#
# You should have received a copy of the GNU Lesser General Public License
# along with this library; if not, write to the Free Software Foundation, Inc.,
# 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
"""A read-only connection to sqlite, for use with the Storm ORM.

To use this, simply import the module somewhere in your application startup.
Importing the module will register a new database scheme with Storm:
"sqlitero".  After importing the module, you can get a read-only connection to
an sqlite database simply by replacing the "sqlite" schema in the URI for the
connection with "sqlitero".  For example:

>>> from storm.locals import *
>>> database = create_database('sqlitero:foo')
>>> store = Store(database)
>>> store.execute("CREATE TABLE person "
...               "(id INTEGER PRIMARY KEY, name VARCHAR); COMMIT")
Traceback (most recent call last):
...
ProgrammingError: This connection is read-only


Why would a readonly connection be useful?  The original development of this
was for a multithreaded application which writes to a SQLite database from
multiple threads.  SQLite only allows a COMMIT operation to be successful when
there are no other instances of the database open.  This works fine if all the
other connections are readonly connections, and reasonably short-lived, because
SQLite will wait until all the readonly connections finish, and then perform
the commit.

However, if two connections attempt to make modifications at the same time,
sqlite will cause both connections to wait for the other to be closed before
allowing the commit to proceed; this usually results in a delay of several
seconds, followed by at least one of the connections raising an exception due
to a timeout.

It is much more desirable to avoid this situation at the application level.
This can be done in two main ways:

 1. Implement a mutex, and have a policy that the mutex must be obtained before
    opening a connection which is going to involve making changes to the
    database.

 2. Have a single thread responsible for all write access to the database.  All
    other threads are only permitted to read from the database.

In either of these implementations, it is useful to be able to avoid programmer
mistakes (which, due to the concurrency issues, may be tricky to reliably
reproduce) by opening the connections which aren't going to be used to make
modifications readonly, such that an error will be raised when any attempt is
made to change the database, whether this happens to be concurrent with a write
or not.

"""

from storm.databases import sqlite
from storm.database import register_scheme
from storm.exceptions import ProgrammingError

class SQLiteReadOnlyConnection(sqlite.SQLiteConnection):
    """A SQLite connection, which only allows read access.
    
    More precisely, this only allows SELECT statements to be performed.

    """
    def __init__(self, *args, **kwargs):
        sqlite.SQLiteConnection.__init__(self, *args, **kwargs)

    def raw_execute(self, statement, *args, **kwargs):
        """Execute a statement.
        
        This first checks that the statement is a SELECT statement.  If it
        isn't, a ProgrammingError is raised.

        """
        # Check that the statement begins with SELECT.
        # Note that we don't have to worry about there being multiple commands
        # in the statement, because pysqlite2 doesn't allow multiple commands
        # to be executed using a Cursor.execute() statement.
        if not statement.lstrip()[:6].upper() == "SELECT":
            raise ProgrammingError, "This connection is read-only"
        return sqlite.SQLiteConnection.raw_execute(self, statement, *args, **kwargs)

class SQLiteReadOnly(sqlite.SQLite):
    """A SQLite database which will produce readonly connections.

    """
    def __init__(self, *args, **kwargs):
        sqlite.SQLite.__init__(self, *args, **kwargs)

    connection_factory = SQLiteReadOnlyConnection

# Register the "sqlitero" scheme.
register_scheme('sqlitero', SQLiteReadOnly)

__test__ = {
'rotest': '''

Test creation of a database, and concurrent modifications, etc.

FIXME - implement
'''
}

if __name__ == '__main__':
    import doctest, sys
    doctest.testmod(sys.modules[__name__])
